﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web;

/// <summary>
/// 名称：access下的分页方案（仿sql存储过程）
/// 作者：cncxz(虫虫)
/// blog：http://cncxz.cnblogs.com
/// </summary>
namespace TuKu.Common
{
    public class AdoPager
    {
        protected string m_ConnString;
        protected OleDbConnection m_Conn;

        public AdoPager()
        {
            CreateConn(string.Empty);
        }
        public AdoPager(string dbPath)
        {
            CreateConn(dbPath);
        }

        private void CreateConn(string dbPath)
        {
           
                string str = System.Configuration.ConfigurationManager.AppSettings["dbConnection"] as string;

                m_Conn = new OleDbConnection(str);
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        public void ConnOpen()
        {
            if (m_Conn.State != ConnectionState.Open)
                m_Conn.Open();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void ConnClose()
        {
            if (m_Conn.State != ConnectionState.Closed)
                m_Conn.Close();
        }

        private string recordID(string query, int passCount)
        {
            OleDbCommand cmd = new OleDbCommand(query, m_Conn);
            string result = string.Empty;
            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    if (passCount < 1)
                    {
                        result += "," + dr.GetInt32(0);
                    }
                    passCount--;
                }
            }
            return result.Substring(1);
        }


        /// <summary>
        /// 获取当前页应该显示的记录，注意：查询中必须包含名为ID的自动编号列，若不符合你的要求，就修改一下源码吧 :)
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">分页容量</param>
        /// <param name="showString">显示的字段</param>
        /// <param name="queryString">查询字符串，支持联合查询</param>
        /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
        /// <param name="orderString">排序规则</param>
        /// <param name="pageCount">传出参数：总页数统计</param>
        /// <param name="recordCount">传出参数：总记录统计</param>
        /// <returns>装载记录的DataTable</returns>
        public DataTable ExecutePager(int pageIndex, int pageSize, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 10;
            if (string.IsNullOrEmpty(showString)) showString = "*";
            if (string.IsNullOrEmpty(orderString)) orderString = "ID desc";
            ConnOpen();
            string myVw = string.Format(" ( {0} ) tempVw ", queryString);
            OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(0) as recordCount from {0} {1}", myVw, whereString), m_Conn);

            recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

            if ((recordCount % pageSize) > 0)
                pageCount = recordCount / pageSize + 1;
            else
                pageCount = recordCount / pageSize;
            OleDbCommand cmdRecord;
            if (pageIndex == 1)//第一页
            {
                cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn);
            }
            else if (pageIndex > pageCount)//超出总页数
            {
                cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn);
            }
            else
            {
                int pageLowerBound = pageSize * pageIndex;
                int pageUpperBound = pageLowerBound - pageSize;
                string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, "ID", myVw, whereString, orderString), pageUpperBound);
                cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where id in ({2}) order by {3} ", showString, myVw, recordIDs, orderString), m_Conn);

            }
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
            DataTable dt = new DataTable();
            dataAdapter.Fill(dt);
            ConnClose();
            return dt;
        }
    }
}